/****** Object:  StoredProcedure [dbo].[factDiscounts]    Script Date: 3.5.2021 15:14:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Kristinn Þór Sigurjónsson / Jónheiður
-- Create date: 2020-11-23
-- Modified Date: 2021-04-13 Use Merge to eliminate risk of duplicate rows
--                2021-05-04 Added ranking to select latest changed row from stg table
-- Description:	SP for fact Discounts
-- =============================================
ALTER PROCEDURE [dbo].[factDiscounts] 

	@CurrentRowID bigint,
	@NewRowID bigint
	
AS
BEGIN
SET NOCOUNT ON;

WITH
/*Temp tables for dimensions*/
tLocation
AS
(SELECT
		dCOM.[CompanyPrefix]
	   ,dLOC.[SK_Location]
	   ,dLOC.[LocationCode]
	FROM [DW].[dLocation] dLOC
	LEFT JOIN [DW].[dCompany] dCOM
		ON dCOM.[SK_Company] = dLOC.[Company]),

tOffers
AS
(SELECT
		dCOM.[CompanyPrefix]
	   ,dOFF.[SK_Offer]
	   ,dOFF.[No_]
	   ,dOFF.[Offer Type]
	FROM [DW].[dOffers] dOFF
	LEFT JOIN [DW].[dCompany] dCOM
		ON dCOM.[SK_Company] = dOFF.[Company]),

tPOSTerminal
AS
(SELECT
		dCOM.[CompanyPrefix]
	   ,dPOT.[SK_POSTerminal]
	   ,dPOT.[No]
	FROM [DW].[dPOSTerminal] dPOT
	LEFT JOIN [DW].[dCompany] dCOM
		ON dCOM.[SK_Company] = dPOT.[Company]),

tFactSalesPosted
AS
(SELECT
		fSAL.SK_Company
	   ,fSAL.SK_Item
	   ,fSAL.SK_Member
	   ,fSAL.SK_Staff
	   ,fSAL.SK_Location
	   ,fSAL.SK_POSTerminal
	   ,fSAL.TransactionNo
	   ,fSAL.[LineNo]
	   ,fSAL.NetSalesAmountLCY
	   ,fSAL.CostAmountLCY
	FROM [DW].[FactSalesPosted] fSAL),

/*Temp table for Discount entries*/
tDiscounts
AS
(SELECT
		sTDE.[CompanyPrefix]
	   ,CAST(sTRH.[Date] AS DATE) AS [Date]
	   ,CAST(DATEADD(mi, DATEDIFF(mi, 0, sTRH.[Time]), 0) AS TIME) AS [Time]
	   ,tLOC.[SK_Location]
	   ,tOFF.[SK_Offer]
	   ,tPOT.[SK_POSTerminal]
	   ,CASE sTDE.[Offer Type]
			WHEN 0 THEN 'Promotion'
			WHEN 1 THEN 'Deal'
			WHEN 2 THEN 'Multibuy'
			WHEN 3 THEN 'Mix&Match'
			WHEN 4 THEN 'Disc. Offer'
			WHEN 5 THEN 'Total Discount'
			WHEN 6 THEN 'Tender Type'
			WHEN 7 THEN 'Item Point'
			WHEN 8 THEN 'Line Discount'
			WHEN 9 THEN 'Customer'
			WHEN 10 THEN 'Infocode'
			WHEN 11 THEN 'Member Point'
			WHEN 12 THEN 'Coupon'
			WHEN 13 THEN 'Total'
			WHEN 14 THEN 'Line'
			ELSE 'No Offer type'
		END AS [Offer Type]
	   ,sTDE.[Receipt No_]
	   ,sTDE.[Line No_]
	   ,CAST(sTDE.[Transaction No_] AS NVARCHAR(100)) AS [TransactionNo]
	   ,sTDE.[Discount Amount]
	   ,sTDE.bigint_timestamp AS [RowID]
	FROM [stg$Trans_ Discount Entry] sTDE
	LEFT OUTER JOIN [stg$Transaction Header] sTRH
		ON sTDE.[Store No_] = sTRH.[Store No_]
		AND sTDE.[POS Terminal No_] = sTRH.[POS Terminal No_]
		AND sTDE.[Transaction No_] = sTRH.[Transaction No_]
		AND sTDE.[CompanyPrefix] = sTRH.[CompanyPrefix]
	LEFT OUTER JOIN [tLocation] tLOC
		ON sTDE.[CompanyPrefix] = tLOC.[CompanyPrefix]
		AND sTDE.[Store No_] = tLOC.[LocationCode]
	LEFT OUTER JOIN [tOffers] tOFF
		ON sTDE.[CompanyPrefix] = tOFF.[CompanyPrefix]
		AND sTDE.[Offer No_] = tOFF.[No_]
		AND sTDE.[Offer Type] = tOFF.[Offer Type]
	LEFT OUTER JOIN [tPOSTerminal] tPOT
		ON sTDE.[CompanyPrefix] = tPOT.[CompanyPrefix]
		AND sTDE.[POS Terminal No_] = tPOT.[No]
	WHERE sTDE.[bigint_timestamp] BETWEEN @CurrentRowID AND @NewRowID),

tDiscountsRanked
AS
(SELECT
		*
	   ,DENSE_RANK() OVER (PARTITION BY [Date], [SK_Location], [SK_POSTerminal], [Line No_], [TransactionNo], [SK_Offer], [Offer Type] ORDER BY [RowID] DESC) AS [Rank]
	FROM tDiscounts)

MERGE [DW].[fDiscounts] AS TARGET USING (SELECT
		COALESCE(dCOM.[SK_Company], -1) AS [Company]
	   ,tDIS.[Date]
	   ,tDIS.[Time]
	   ,COALESCE(tDIS.[SK_Location], -1) AS [SK_Location]
	   ,COALESCE(tDIS.[SK_Offer], -1) AS [SK_Offer]
	   ,COALESCE(tDIS.[SK_POSTerminal], -1) AS [SK_POSTerminal]
	   ,COALESCE(tSAL.[SK_Item], -1) AS [SK_Item]
	   ,COALESCE(tSAL.[SK_Member], -1) AS [SK_Member]
	   ,COALESCE(tSAL.[SK_Staff], -1) AS [SK_Staff]
	   ,tDIS.[Offer Type]
	   ,tDIS.[Receipt No_]
	   ,tDIS.[Line No_]
	   ,tDIS.[TransactionNo]
	   ,tDIS.[Discount Amount]
	   ,tSAL.NetSalesAmountLCY AS SalesAmount
	   ,tSAL.CostAmountLCY AS CostAmount
	   ,tDIS.[RowID]
	FROM tDiscountsRanked tDIS
	LEFT JOIN [DW].[dCompany] dCOM
		ON tDIS.[CompanyPrefix] = dCOM.[CompanyPrefix]
	LEFT JOIN tFactSalesPosted tSAL
		ON tDIS.SK_Location = tSAL.SK_Location
		AND tDIS.SK_POSTerminal = tSAL.SK_POSTerminal
		AND tDIS.TransactionNo = tSAL.TransactionNo
		AND tDIS.[Line No_] = tSAL.[LineNo]
		AND dCOM.[SK_Company] = tSAL.SK_Company
	WHERE [Rank] = 1
	) AS Source
ON Target.[Company] = Source.[Company]
	AND Target.[Date] = Source.[Date]
	AND Target.[SK_Location] = Source.[SK_Location]
	AND Target.[SK_POSTerminal] = Source.[SK_POSTerminal]
	AND Target.[Line No_] = Source.[Line No_]
	AND Target.[TransactionNo] = Source.[TransactionNo]
	AND Target.[SK_Offer] = Source.[SK_Offer]
	AND Target.[Offer Type] = Source.[Offer Type]
WHEN MATCHED
	THEN UPDATE
		SET [Time] = Source.[Time]
		   ,[Receipt No_] = Source.[Receipt No_]
		   ,[Discount Amount] = Source.[Discount Amount]
		   ,[RowID] = Source.[RowID]
		   ,[Batchdate] = GETUTCDATE()
		   ,[SK_Item] = Source.[SK_Item]
		   ,[SK_Member] = Source.[SK_Member]
		   ,[SK_Staff] = Source.[SK_Staff]
		   ,[SalesAmount] = Source.[SalesAmount]
		   ,[CostAmount] = Source.[CostAmount]

WHEN NOT MATCHED BY TARGET
	THEN INSERT ([Company]
		, [Date]
		, [Time]
		, [SK_Location]
		, [SK_Offer]
		, [SK_POSTerminal]
		, [Offer Type]
		, [Receipt No_]
		, [Line No_]
		, [TransactionNo]
		, [Discount Amount]
		, [RowID]
		, [Batchdate]
		, [SK_Item]
		, [SK_Member]
		, [SK_Staff]
		, [SalesAmount]
		, [CostAmount])
			VALUES (Source.[Company], Source.[Date], Source.[Time], Source.[SK_Location], Source.[SK_Offer], Source.[SK_POSTerminal], Source.[Offer Type], Source.[Receipt No_], Source.[Line No_], Source.[TransactionNo], Source.[Discount Amount], Source.[RowID], GETUTCDATE(), Source.[SK_Item], Source.[SK_Member], Source.[SK_Staff], Source.[SalesAmount], Source.[CostAmount])
;

END

SELECT
	'RowCount' = @@rowcount